# -*- coding: utf-8 -*-
# @Author  : pony.guan
# @Time    : 2022/5/25 10:55
# Description:  Booktopia 理库

import os
import time
import json
import logging
from logging.handlers import TimedRotatingFileHandler
from datetime import datetime, timedelta
from itertools import zip_longest
from contextlib import contextmanager
from kafka import KafkaProducer
import yaml
import pymysql

log_file = os.path.join('logs', 'log')

if not os.path.exists('logs'):
    os.makedirs('logs')

log = logging.getLogger()
log.setLevel(logging.WARNING)
formatter = logging.Formatter(
    '%(asctime)s %(message)s')

log_file_handler = TimedRotatingFileHandler(filename=log_file, when="D", interval=1, backupCount=7)
log_file_handler.setFormatter(formatter)
log_file_handler.setLevel(logging.DEBUG)
log.addHandler(log_file_handler)


@contextmanager
def cursor_context(ip: str, db: str):
    config = {'host': ip, 'port': 3306, 'user': 'root', 'passwd': 'root', 'db': db, 'charset': 'utf8'}
    conn = pymysql.connect(**config)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        yield cursor
        conn.commit()
    except:
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()


def bin_code_rank(x1: int, x2: int, timestamp: int, tasks_quantity: int, area_type: str) -> tuple:
    """
    @desc 箱子活动次数进行排序
    :return:
    """
    with cursor_context(ip, 'wms_core') as sc:
        sql = "select id, code from ks_bin_space where id in " \
              "(select ks_bin_space_id from ks_shelf_storage where ks_map_location_id in  " \
              "(select  id from ks_map_location where x between '%s' and '%s'));"
        sc.execute(sql, (x1, x2))

        rows = sc.fetchall()
        # 该区的所有库位
        space_id_map_space_code = {row['id']: row['code'] for row in rows}
        space_codes = [row['code'] for row in rows]
        space_code_map_sapce_id = {row['code']: row['id'] for row in rows}

    with cursor_context(ip, 'booktopia') as sc:
        sql1 = "select id, code, space_code from ks_bin where code  is not null and status='BIN_NORMAL' and space_code in %s"
        sc.execute(sql1, (space_codes,))
        rows1 = sc.fetchall()

        bin_codes = [row['code'] for row in rows1] + [None]
        bin_code_map_space_code_nums = {row['code']: {'space_code': row['space_code'], 'nums': 0, 'bin_id': row['id'],
                                                      'space_id': space_code_map_sapce_id[row['space_code']]}
                                        for row in rows1}
        sql2 = "select bin_code, count(1) as nums from ks_outbound_order_detail where bin_code in %s " \
               "and created >= %s group by bin_code limit %s"
        sc.execute(sql2, (bin_codes, timestamp, tasks_quantity))
        rows2 = sc.fetchall()
        bin_code_map_nums = {row['bin_code']: row['nums'] for row in rows2}
        for bin_code in bin_code_map_space_code_nums.keys():
            if bin_code in bin_code_map_nums.keys():
                bin_code_map_space_code_nums[bin_code]['nums'] = bin_code_map_nums[bin_code]

        # 热库区升序排列
        if area_type == 'hot':
            rank_data = dict(
                sorted(bin_code_map_space_code_nums.items(), key=lambda x: int(x[-1]['nums']), reverse=False))
        # 冷库区降序排列
        if area_type == 'cold':
            rank_data = dict(
                sorted(bin_code_map_space_code_nums.items(), key=lambda x: int(x[-1]['nums']), reverse=True))

        # 查找该区所有的空库位
        sql3 = "select id from ks_bin"
        sc.execute(sql3)

        rows3 = sc.fetchall()
        # 有箱子的库位
        spaces_id_with_bin = [row['id'] for row in rows3]
        # 该区的空库位
        space_id_map_space_code_without_bin = {space_id: space_id_map_space_code[space_id] for space_id in
                                               space_id_map_space_code.keys() if space_id not in spaces_id_with_bin}
        return rank_data, space_id_map_space_code_without_bin


def get_bins(hot_cold_ratio: str, tasks_quantity: int, interval_time: int) -> tuple:
    today = datetime.now()
    day = today - timedelta(days=interval_time)
    timestamp_ = int(datetime.strptime(day.strftime('%Y-%m-%d'), '%Y-%m-%d').timestamp() * 1000)

    hot_x1, hot_x2 = hot_cold_ratio_info[hot_cold_ratio]['hot_area'].values()
    cold_x1, cold_x2 = hot_cold_ratio_info[hot_cold_ratio]['cold_area'].values()
    hot_asc_data, hot_space_without_bin = bin_code_rank(hot_x1,
                                                        hot_x2,
                                                        timestamp_,
                                                        tasks_quantity,
                                                        area_type='hot')

    cold_desc_data, cold_space_without_bin = bin_code_rank(cold_x1,
                                                           cold_x2,
                                                           timestamp_,
                                                           tasks_quantity,
                                                           area_type='cold')
    hot_spaces_id_without_bin = list(hot_space_without_bin.keys())
    cold_spaces_id_without_bin = list(cold_space_without_bin.keys())
    min_len = min(len(hot_spaces_id_without_bin), len(cold_spaces_id_without_bin))
    print('---Analytical data ...----')
    hot_spaces_id_without_bin = hot_spaces_id_without_bin[: min_len]
    cold_spaces_id_without_bin = cold_spaces_id_without_bin[: min_len]

    # 保证热冷库区移动箱子个数相等, 结构为： [(热取箱子，冷区箱子)]
    aligin_data = list(zip(hot_asc_data, cold_desc_data))

    if len(aligin_data) >= tasks_quantity:
        aligin_data = aligin_data[: tasks_quantity]
    hot_bin_codes = [i[0] for i in aligin_data]
    cold_bin_codes = [i[1] for i in aligin_data]
    hot_asc_data = {bin_code: hot_asc_data[bin_code] for bin_code in hot_asc_data if bin_code in hot_bin_codes}
    cold_desc_data = {bin_code: cold_desc_data[bin_code] for bin_code in cold_desc_data if bin_code in cold_bin_codes}
    print(hot_asc_data)
    # 热区中需要移动的箱子
    hot_bin_codes = [i[0] for i in aligin_data]
    # 冷区中需要移动的箱子
    cold_bin_codes = [i[1] for i in aligin_data]
    hot_asc_data = {bin_code: hot_asc_data[bin_code] for bin_code in hot_asc_data if bin_code in hot_bin_codes}
    cold_desc_data = {bin_code: cold_desc_data[bin_code] for bin_code in cold_desc_data if bin_code in cold_bin_codes}
    # 热区箱子移动到冷区，目标库位优先考虑冷区空库位, 没用空库位时，才库位互换

    hot_area_move_info = list(zip_longest(list(hot_asc_data.items()), cold_spaces_id_without_bin))
    # hot_area_move_info1：热区移到冷区空库位信息
    hot_area_move_info1 = {}
    for _ in hot_area_move_info:
        if _[0] and _[-1]:
            bin_code = _[0][0]
            bin_id = _[0][1]['bin_id']
            from_space_id = _[0][1]['space_id']
            from_space_code = _[0][1]['space_code']
            nums = _[0][1]['nums']
            # 热区移到冷区不是空位时，已经自动发生冷区往热区中移库的箱子
            to_space_id = _[-1]
            hot_area_move_info1[bin_code] = {
                'bin_id': bin_id,
                'from_space_id': from_space_id,
                'from_space_code': from_space_code,
                'nums': nums,
                'to_space_id': to_space_id,
                'to_space_code': cold_space_without_bin[to_space_id]
            }

    # 冷区箱子移动到冷区，目标库位优先考虑热区空库位，没用空库位时，才库位互换
    cold_area_move_info = list(zip_longest(list(cold_desc_data.items()), hot_spaces_id_without_bin))

    # hot_area_move_info1：冷区移到热区空库位信息
    cold_area_move_info1 = {}
    for _ in cold_area_move_info:
        if _[0] and _[-1]:
            bin_code = _[0][0]
            bin_id = _[0][1]['bin_id']
            from_space_id = _[0][1]['space_id']
            from_space_code = _[0][1]['space_code']
            nums = _[0][1]['nums']
            # 热区移到冷区不是空位时，已经自动发生冷区往热区中移库的箱子
            to_space_id = _[-1]
            cold_area_move_info1[bin_code] = {
                'bin_id': bin_id,
                'from_space_id': from_space_id,
                'from_space_code': from_space_code,
                'nums': nums,
                'to_space_id': to_space_id,
                'to_space_code': hot_space_without_bin[to_space_id]
            }
    merge1 = dict(**hot_area_move_info1, **cold_area_move_info1)
    bins1 = [{'binId': i['bin_id'], 'locationId': i['to_space_id']} for i in merge1.values()]
    bins1_ = [
        {'bin_code': i, 'from_space_code': merge1[i]['from_space_code'], 'to_space_code': merge1[i]['to_space_code']}
        for i in merge1.keys()]
    # 热冷库区需要对换的信息
    hot_remain_bin_codes = [i for i in hot_bin_codes if i not in hot_area_move_info1.keys()]
    cold_remain_bin_codes = [i for i in cold_bin_codes if i not in cold_area_move_info1.keys()]

    bins2 = []
    bins2_ = []
    for hot_bin_code, cold_bin_code in zip(hot_remain_bin_codes, cold_remain_bin_codes):
        hot_bin_id = hot_asc_data[hot_bin_code]['bin_id']
        hot_form_space_code = hot_asc_data[hot_bin_code]['space_code']
        to_cold_space_id = cold_desc_data[cold_bin_code]['space_id']
        to_cold_space_code = cold_desc_data[cold_bin_code]['space_code']

        cold_bin_id = cold_desc_data[cold_bin_code]['bin_id']
        cold_form_space_code = cold_desc_data[cold_bin_code]['space_code']
        to_hot_space_id = hot_asc_data[hot_bin_code]['space_id']
        to_hot_space_code = hot_asc_data[hot_bin_code]['space_code']

        bins2.extend(
            [
                {'binId': hot_bin_id, 'locationId': to_cold_space_id},
                {'binId': cold_bin_id, 'locationId': to_hot_space_id}
            ]
        )

        bins2_.extend(
            [
                {'bin_code': hot_bin_code, 'from_space_code': hot_form_space_code, 'to_space_code': to_cold_space_code},
                {'bin_code': cold_bin_code, 'from_space_code': cold_form_space_code, 'to_space_code': to_hot_space_code}
            ]
        )

    print('---Parsing complete ...---')
    return bins1 + bins2, bins1_ + bins2_


def send_message_to_kafka(bins: list) -> None:
    bootstrap_servers = [f'{ip}:9092', ]
    producer = KafkaProducer(
        bootstrap_servers=bootstrap_servers,
        value_serializer=lambda m: json.dumps(m).encode())

    message = {
        "msgBody": {
            "actionModel": 0,
            "bins": bins
        },
        "msgId": int(time.time()),  # 保证唯一数字
        "msgType": 9220
    }

    producer.send('ks_mission_control', message)
    producer.flush()


with open('config.yml') as f:
    json_ = yaml.load(f.read(), Loader=yaml.FullLoader)

ip = json_['ip']
hot_cold_ratio_info = json_['hot_cold_ratio_info']
# hot_cold_ratio = json_['move_location_checked']['hot_cold_ratio']
# tasks_quantity = json_['move_location_checked']['tasks_quantity']
# interval_time = json_['move_location_checked']['interval_time']

# ip = '172.20.8.146'
#
# hot_cold_ratio_info = {
#     '2:8': {
#         'hot_area': {'x1': 10, 'x2': 12},
#         'cold_area': {'x1': 13, 'x2': 15}
#     },
#
#     '5:5': {
#         'hot_area': {'x1': 10, 'x2': 19},
#         'cold_area': {'x1': 25, 'x2': 30}
#     }
# }


if __name__ == '__main__':
    # 配置
    # # 冷热比
    # hot_cold_ratio = '2:8'
    # # 理库任务数量
    # tasks_quantity = 20
    # # 最近多少天
    # interval_time = 5

    # 安装依赖包
    # print('---Install dependencies---')
    # os.system("pip3 install "
    #           "PyYAML "
    #           "PyMySQL==0.9.3  "
    #           "kafka-python "
    #           "-i http://pypi.douban.com/simple/")
    #
    # print('---Install dependencies complete---')

    print('---query database ...----')
    bins, bins_ = get_bins(hot_cold_ratio, tasks_quantity, interval_time)
    print(len(bins))
    print(bins_)
    print('---send message to kafka ...----')

    log_msg = '\n'.join([ '\t' + str(_) for _ in bins_])

    log.warning('move_location_msg:' + '\n' + log_msg)

    send_message_to_kafka(bins)
    print('---Assignment task completed----')
